#
#用于修正允许零库存和负库存出货早期程序bug带来的问题数据
#零库存商品直接出货，除了库存数量外成本和价格数据斗没有
#
#需要在.bahs_profile设置环境变量
#export PSI_PASSWORD=xxxxxxx
#export RDS_AGENT=yyyy
#
#手工在命令行运行 ruby fix_inventory_detail.rb
#
require 'mysql2'

    rds = Mysql2::Client.new(:host => ENV['RDS_AGENT'], :username => "psi_root", :port => '1401', :password => ENV['PSI_PASSWORD'])

    goodsName = {};
    goodsPrice = {};
    goodsCategory = {};
    sql = "select * from psi.t_goods"
    res = rds.query(sql)
    res.each do |res|
        id = res['id'];
        name = res['name'];
        purchase_price = res['purchase_price'];
	category_id = res['category_id'];
        goodsName.store(id,name);
        goodsPrice.store(id,purchase_price);
	goodsCategory.store(id,category_id);
    end
    #goodsName.each { |id,name| puts "#{id} #{name}" }

    #在收发明细表上曾有负值数量库存金额却未为负的商品列表
    negativeQtyGoods = {};
    sql = "select * from psi.t_goods where id in (select distinct goods_id from psi.t_inventory_detail where balance_count < 0 and balance_money >= 0 and warehouse_id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963')"
    res = rds.query(sql)
    res.each do |res|
        id = res['id'];
        purchase_price = res['purchase_price'];
        negativeQtyGoods.store(id,purchase_price);
    end
    puts "===negative qty goods total: #{negativeQtyGoods.count}"
    #negativeQtyGoods.each { |id, purchase_price| puts id+' '+sprintf("%.2f",purchase_price); }
    

    #在收发明细表上曾有零出库或入库成本单价，零入库或出库数量的商品列表
    questionGoods = {};
	sql = "select distinct goods_id from psi.t_inventory_detail where 
		( balance_count < 0 or balance_price = 0 or in_count = 0 or out_count <= 0 or in_price <=0 or out_price <=0 )
		 and balance_money >= 0 and warehouse_id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963'
	"
    res = rds.query(sql)
    res.each do |res|
        gid = res['goods_id'];
        price = goodsPrice[gid];
        questionGoods.store(gid,price);
    end
    puts "===questioned goods total: #{questionGoods.count}"
    #questionGoods.each { |gid, price| puts "#{goodsName[gid]} #{gid} #{sprintf("%.2f", price)}" }

    tflag = 0;
    questionGoods.each { |gid, purchase_price| 

        ##tag questioned record ONLY ONCE
        #sqlu = "update psi.t_inventory_detail set fixed='tofix' where warehouse_id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963' and goods_id='#{gid}'"
        #resu = rds.query(sqlu)

        #to observer examples
        if (tflag==0) then
            puts "example goods_id: #{gid}"
            sql2 = "select * from psi.t_inventory_detail where warehouse_id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963' and goods_id='#{gid}' order by id desc"
            res2 = rds.query(sql2)

            settle_flag=true
            res2.each do |r2|
                if r2['out_count'].nil? then
                    #入库记录
                    #puts "IN >>>  #{r2['id']} #{sprintf('%.2f',r2['balance_count'])} #{r2['balance_price']} #{r2['balance_money']} | #{sprintf('%.2f',r2['in_count'])} #{r2['in_price']} #{r2['in_money']} #{goodsName[r2['goods_id']]} #{sprintf('%.2f',goodsPrice[r2['goods_id']])} "
                elsif r2['in_count'].nil? then
                    #出库记录
                    #puts "OUT<<<  #{r2['id']} #{sprintf('%.2f',r2['balance_count'])} #{r2['balance_price']} #{r2['balance_money']} | #{sprintf('%.2f',r2['out_count'])} #{r2['out_price']} #{r2['out_money']} #{goodsName[r2['goods_id']]} #{sprintf('%.2f',goodsPrice[r2['goods_id']])} "
                else
                    puts "Something wrong"
                end

                if settle_flag then
                    #更新总帐余额
                else
                    settle_flag = false
                end
            end
        end
        tflag+=1;
    }


    #在库存总帐上却一直没有收发明细的商品
    notUsedGoods = {};
    zeroWithDetailGoods = {};
    sql = "select * from psi.t_inventory where balance_count = 0 and balance_price = 0 and warehouse_id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963'"
    res = rds.query(sql);
    res.each do |res|
        goods_id = res['goods_id']
        sql2 = "select id from psi.t_inventory_detail where warehouse_id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963' and goods_id='#{goods_id}'"
        res2 = rds.query(sql2)
        if res2.count==0 then
	    #if (goodsCategory[goods_id]!='E73DB32C-CB6E-6FE8-F260-DE23D7971E4C' &&  goodsCategory[goods_id] != 'C4CCDAD3-ACBE-80F4-F0A8-4A42F87A5948' && goodsCategory[goods_id] != 'AADE81B7-2949-C65B-3438-4ECBEB0F0294') then
	    sql3 = "delete from psi.t_inventory where goods_id='#{goods_id}' and warehouse_id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963'"
	    res3 = rds.query(sql3)
	    notUsedGoods.store(goods_id,goodsPrice[goods_id]);
	    #end
        else
            zeroWithDetailGoods.store(goods_id,res2.count);
        end
    end

    #puts "===not used goods total to delete: #{notUsedGoods.count}"
    #notUsedGoods.each { |id, purchase_price| puts "#{id} #{purchase_price} #{goodsName[id]}" }

    puts "===zero with detail goods: #{zeroWithDetailGoods.count}"
    #zeroWithDetailGoods.each { |id, count| puts "#{count} #{id} #{goodsName[id]}" }

rds.close
